package com.giscloud.commons.sql;


import cn.hutool.core.collection.CollectionUtil;
import com.giscloud.commons.utils.ObjectHelper;
import com.giscloud.commons.utils.StringUtils;

import java.util.Arrays;
import java.util.List;

/**
 * @Author:giscloud
 * @Description:动态拼装sql的where条件
 * @Date: 13:05 2018/12/5
 * @Modified By:
 */
public class Specification {
    private StringBuilder where = new StringBuilder();
    private String groupBy;
    private String having;
    private String orderBy;

    public StringBuilder getWhere() {
        return where;
    }

    public void setWhere(StringBuilder where) {
        this.where = where;
    }

    public String getGroupBy() {
        return groupBy;
    }

    public void setGroupBy(String groupBy) {
        this.groupBy = groupBy;
    }

    public String getHaving() {
        return having;
    }

    public void setHaving(String having) {
        this.having = having;
    }

    public String getOrderBy() {
        return orderBy;
    }

    public void setOrderBy(String orderBy) {
        this.orderBy = orderBy;
    }

    public Specification addOrderBy(String sort, String order) {
        if (!StringUtils.isEmpty(sort) && !StringUtils.isEmpty(order)) {
            this.orderBy = ObjectHelper.underscoreName(sort) + " " + order;
        }
        return this;
    }

    public Specification orLike(String value, String columns) {
        if (!StringUtils.isEmpty(value)) {
            StringBuffer strBuf = new StringBuffer("");
            for (String column : columns.split(",")) {
                strBuf.append(ObjectHelper.underscoreName(column) + " like '%"
                        + value + "%' or ");
            }
            String orLikeStr = strBuf.substring(0, strBuf.lastIndexOf("or"));
            where.append(" and (" + orLikeStr + ")");
        }
        return this;
    }

    public Specification eq(String column, String value) {
        if (!StringUtils.isEmpty(value)) {
            where.append(" and " + ObjectHelper.underscoreName(column) + " = '"
                    + sqlParam(value) + "'");
        }
        return this;
    }

    public Specification ne(String column, String value) {
        if (!StringUtils.isEmpty(value)) {
            where.append(" and " + ObjectHelper.underscoreName(column)
                    + " != '" + sqlParam(value) + "'");
        }
        return this;
    }

    public Specification like(String column, String value) {
        if (!StringUtils.isEmpty(value)) {
            where.append(" and " + ObjectHelper.underscoreName(column)
                    + " like '%" + sqlParam(value) + "%'");
        }
        return this;
    }

    public Specification notLike(String column, String value) {
        if (!StringUtils.isEmpty(value)) {
            where.append(" and " + ObjectHelper.underscoreName(column)
                    + " not like '%" + sqlParam(value) + "%'");
        }
        return this;
    }

    public Specification in(String column, String... values) {
        if (!StringUtils.isEmpty(values)) {
            where.append(" and " + ObjectHelper.underscoreName(column)
                    + " in (" + inValuesString(values) + ")");
        }
        return this;
    }
    public Specification in(String column, List values) {
        if (CollectionUtil.isNotEmpty(values)) {
            where.append(" and ").append(buildLogicIn(column,values));
        }
        return this;
    }
    public Specification notIn(String column, List values) {
        if (CollectionUtil.isNotEmpty(values)) {
            where.append(" and ").append(buildLogicIn(column,values));
        }
        return this;
    }
    public Specification notIn(String column, String... values) {
        if (!StringUtils.isEmpty(values)) {
            where.append(" and " + ObjectHelper.underscoreName(column)
                    + " not in (" + inValuesString(values) + ")");
        }
        return this;
    }

    public Specification gt(String column, String value) {
        if (!StringUtils.isEmpty(value)) {
            where.append(" and " + ObjectHelper.underscoreName(column) + " > '"
                    + sqlParam(value) + "'");
        }
        return this;
    }

    public Specification gte(String column, String value) {
        if (!StringUtils.isEmpty(value)) {
            where.append(" and " + ObjectHelper.underscoreName(column)
                    + " >= '" + sqlParam(value) + "'");
        }
        return this;
    }

    public Specification lt(String column, String value) {
        if (!StringUtils.isEmpty(value)) {
            where.append(" and " + ObjectHelper.underscoreName(column) + " < '"
                    + sqlParam(value) + "'");
        }
        return this;
    }

    public Specification lte(String column, String value) {
        if (!StringUtils.isEmpty(value)) {
            where.append(" and " + ObjectHelper.underscoreName(column)
                    + " <= '" + sqlParam(value) + "'");
        }
        return this;
    }

    public Specification between(String column, String from, String to) {
        if (StringUtils.isEmpty(from) && StringUtils.isEmpty(to)) {
            return this;
        }
        if (StringUtils.isEmpty(to)) {
            where.append(" and " + ObjectHelper.underscoreName(column)
                    + " >= '" + sqlParam(from) + "'");
        } else if (StringUtils.isEmpty(from)) {
            where.append(" and " + ObjectHelper.underscoreName(column)
                    + " <= '" + sqlParam(to) + "'");
        } else {
            where.append(" and " + ObjectHelper.underscoreName(column)
                    + " between '" + sqlParam(from) + "' and '" + sqlParam(to)
                    + "'");
        }
        return this;
    }

    public Specification buildWhereSql(String column,String rule,String value, String from, String to) {
        switch (rule){
            case "in":
                List<String> values = Arrays.asList(value.split(","));
                return in(column,values);
            case "not in":
                values = Arrays.asList(value.split(","));
                return notIn(column,values);
            case "like":
                return like(column,value);
            case "not like":
                return notLike(column,value);
            case "=":
                return eq(column,value);
            case "<":
                return lt(column,value);
            case ">":
                return gt(column,value);
            case ">=":
                return gte(column,value);
            case "<=":
                return lte(column,value);
            case ">=|<=":
                return between(column,from,to);
            case ">|<":
                return betweenExcludingEnds(column,from,to);
            case "!=":
                return ne(column,value);
            default:
                return this;
        }
    }
        /**
     * 包含不包括两端
     * @param column
     * @param from
     * @param to
     * @return
     */
    public Specification betweenExcludingEnds(String column, String from, String to) {
        if (StringUtils.isEmpty(from) && StringUtils.isEmpty(to)) {
            return this;
        }
        if (StringUtils.isEmpty(to)) {
            where.append(" and " + ObjectHelper.underscoreName(column)
                    + " > '" + sqlParam(from) + "'");
        } else if (StringUtils.isEmpty(from)) {
            where.append(" and " + ObjectHelper.underscoreName(column)
                    + " < '" + sqlParam(to) + "'");
        }
        return this;
    }

    public String sql() {
        StringBuilder sql = new StringBuilder("");
        final int a = 4;
        final int b = 5;
        if (where.length() > a) {
            sql.append(" " + where.substring(b));
        }
        if (!StringUtils.isEmpty(groupBy)) {
            sql.append(" group by " + groupBy);
        }
        if (!StringUtils.isEmpty(having)) {
            sql.append(" having " + having);
        }
        if (!StringUtils.isEmpty(orderBy)) {
            sql.append(" order by " + orderBy);
        }
        return sql.toString();
    }

    @Override
    public String toString() {
        return sql();
    }

    private static String inValuesString(String[] values) {
        StringBuilder string = new StringBuilder();
        for (String value : values) {
            if (StringUtils.isEmpty(value)) {
                continue;
            }
            string.append('\'');
            string.append(value);
            string.append('\'');
            string.append(',');
        }
        if (string.length() > 0) {
            string.deleteCharAt(string.length() - 1);
        }
        return string.toString();
    }

    private static String sqlParam(String sqlParam) {
        return sqlParam.replaceAll("([';]+|(--)+)", "");
    }
    /**
     * 构造in语句，若valueList超过1000时，该函数会自动拆分成多个in语句
     *
     * @param column
     * @param values
     * @return item in (valueList)
     */
    public static String buildLogicIn(String column, List values) {
        int n = (values.size() - 1) / 1000;
        StringBuffer rtnStr = new StringBuffer();
        Object obj = values.get(0);
        boolean isString = false;
        if (obj instanceof Character || obj instanceof String) {
            isString = true;
        }
        String tmpStr;
        for (int i = 0; i <= n; i++) {
            int size = i == n ? values.size() : (i + 1) * 1000;
            if (i > 0) {
                rtnStr.append(" or ");
            }
            rtnStr.append(ObjectHelper.underscoreName(column) + " in (");
            if (isString) {
                StringBuffer tmpBuf = new StringBuffer();
                for (int j = i * 1000; j < size; j++) {
                    tmpStr = values.get(j).toString().replaceAll("'", "''");
                    tmpBuf.append(",'").append(tmpStr).append("'");
                }
                tmpStr = tmpBuf.substring(1);
            } else {
                tmpStr = values.subList(i * 1000, size).toString();
                tmpStr = tmpStr.substring(1, tmpStr.length() - 1);
            }
            rtnStr.append(tmpStr);
            rtnStr.append(")");
        }
        if (n > 0) {
            return "(" + rtnStr.toString() + ")";
        } else {
            return rtnStr.toString();
        }
    }

    /**
     * 构造not in语句，若valueList超过1000时，该函数会自动拆分成多个in语句
     *
     * @param column
     * @param values
     * @return item in (valueList)
     */
    public static String buildLogicNotIn(String column, List values) {
        int n = (values.size() - 1) / 1000;
        StringBuffer rtnStr = new StringBuffer();
        Object obj = values.get(0);
        boolean isString = false;
        if (obj instanceof Character || obj instanceof String) {
            isString = true;
        }
        String tmpStr;
        for (int i = 0; i <= n; i++) {
            int size = i == n ? values.size() : (i + 1) * 1000;
            if (i > 0) {
                rtnStr.append(" and ");
            }
            rtnStr.append(ObjectHelper.underscoreName(column) + " not in (");
            if (isString) {
                StringBuffer tmpBuf = new StringBuffer();
                for (int j = i * 1000; j < size; j++) {
                    tmpStr = values.get(j).toString().replaceAll("'", "''");
                    tmpBuf.append(",'").append(tmpStr).append("'");
                }
                tmpStr = tmpBuf.substring(1);
            } else {
                tmpStr = values.subList(i * 1000, size).toString();
                tmpStr = tmpStr.substring(1, tmpStr.length() - 1);
            }
            rtnStr.append(tmpStr);
            rtnStr.append(")");
        }
        if (n > 0) {
            return "(" + rtnStr.toString() + ")";
        } else {
            return rtnStr.toString();
        }
    }

    public static void main(String[] args) {
        Specification specification = new Specification();
        specification.buildWhereSql("test","in","1,2,3","","");
        System.out.println(specification.toString());
    }
}
